#coding=utf-8
import web
import sqlite3
import json
import config
import hashlib

web.config.debug = False # 关闭调试模式才能正常使用session，否则按http://webpy.org/cookbook/session_with_reloader.zh-cn方法操作
urls = (
    '/(.*)', 'hello'
)
from web.template import ALLOWED_AST_NODES
ALLOWED_AST_NODES.append('Constant')
render = web.template.render('templates/')
app = web.application(urls, globals())
web.config.session_parameters['cookie_name'] = 'JSESSIONID'
session = web.session.Session(app, web.session.DiskStore('sessions'), initializer={'account': 0})

# 查询结果转换为字典组
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

class hello:        
    def GET(self, name):
        web.header('Content-Type', 'text/html;charset=UTF-8')
        if not name: 
            web.redirect('/static/login.html')
        # 评卷列表页
        elif name=="pingjuan_list/":
            session.account += 1
            return render.pingjuan_list(session.account)
        # 退出
        elif name=="logout/":
            session.kill()
            return "logout ok"
        else:
            web.redirect('/static/index.html')
        # return 'Hello, ' + name + '!'
    def POST(self, name):
        web.header('Content-Type', 'text/html;charset=UTF-8')
        conn = sqlite3.connect('./database/data.sqlite')
        if not name: 
            web.redirect('/static/login.html')
        
        # 获取tables初始化需要的数据，一次性打包返回
        if name=='tables/getInitDatas/': 
            conn.row_factory = dict_factory
            cursor = conn.cursor()
            retData = {}
            # 部门
            depts = cursor.execute("SELECT    id,name,type,is_link_stud,is_link_jxdwjs,is_link_znbmgzry      from dept")
            retData['depts'] = cursor.fetchall()
            # 所有被评价人
            beipingrens = cursor.execute("SELECT   id,name,dept_id,level     from beipingren")
            retData['beipingrens'] = cursor.fetchall()

            return json.dumps(retData)
        
        # 获取tables被评人信息
        elif name=="tables/getBeipingrens/":
            i = web.input()
            dept_id = i.get('dept_id')
            level   = i.get('level')
            conn.row_factory = dict_factory
            cursor = conn.cursor()
            retData = {}
            retData["form"]={"dept_id":dept_id,"level":level}
            sql = ""
            if dept_id == "_" and level != "_": # 按部门查找
                sql = "SELECT id,name,dept_id,level FROM beipingren WHERE level=%s" % (level)
            elif dept_id != "_" and level == "_":  # 按级别查找
                sql = "SELECT id,name,dept_id,level FROM beipingren WHERE dept_id=%s" % (dept_id)
            elif dept_id != "_" and level != "_":  # 按部门、级别查找
                sql = "SELECT id,name,dept_id,level FROM beipingren WHERE dept_id=%s AND level=%s" % (dept_id,level)
            else:  # 找出所有被评价人
                sql = "SELECT id,name,dept_id,level FROM beipingren"
            
            rs = cursor.execute(sql)
            retData['beipingrens'] = cursor.fetchall()
            retData['sql'] = sql
            return json.dumps(retData)

        # 获取被评人评价数据
        elif name=="tables/getBeipingrenScores/":
            i = web.input()
            id = i.get('id')
            if not id:
                return { "result":0, "info":"请求参数错误。err 223" }
            # conn.row_factory = dict_factory
            cursor = conn.cursor()
            retData = {}
            retData["form"]={"id":id}
            colStr = ','.join(config.scoreCols);    # bp_uid,p_type,p_order,n1,n2,n3,n4,n5,n6,q1,q2,q3,q4,q5,j1,j2,j3,j4,j5
            sql = "SELECT %s FROM score WHERE bp_uid=%s" % (colStr,id)
            rs = cursor.execute(sql)
            retData['scores'] = cursor.fetchall()
            retData['sql'] = sql
            retData['status'] = 1
            retData['colStr'] = colStr # 返回字段配置方便前端处理
            return json.dumps(retData)

        # 保存某被评人成绩
        elif name=="tables/postScores/":
            i = web.input()
            id = i.get('id')
            scoreStr  = i.get('scoreStr')
            scoreDict = json.loads(scoreStr) #得到list
            if len(scoreDict) == 0 :
                return { "result":0, "info":"没有获取数据。err 12341" }

            cursor = conn.cursor()

            # 首先删除一次此人成绩
            sqlDel = "DELETE FROM score WHERE bp_uid=%s" % (id)
            cursor.execute(sqlDel);
            conn.commit()

            # 构造一次性插入多条数据的insert sql语句 insert into score(a,b,c,d) values ('1','2','3','4'),('5','6','7','8'),('9','10','11','12')
            sqlArr = [];
            colStr = ','.join(config.scoreCols);    # bp_uid,p_type,p_order,n1,n2,n3,n4,n5,n6,q1,q2,q3,q4,q5,j1,j2,j3,j4,j5
            sqlValueArr = []
            for val in scoreDict: # val也是list
                valStr = "','".join(list(map(str, val))) # 组装成逗号分割的字符串序列  如"'1','2','3','4'"
                sqlValueOne = " ('%s', '%s') " % (id,valStr ) #"('id','1','2','3','4')"
                sqlValueArr.append(  sqlValueOne )
            
            sqlValueStr = ','.join(sqlValueArr) # "('1','2','3','4'),('5','6','7','8'),('9','10','11','12')"
            sqlStr = "INSERT INTO score(%s) values %s " % (colStr,sqlValueStr)

            cursor.execute(sqlStr);
            conn.commit()

            retData = {
                # 'cfg': ','.join(config.scoreCols),
                'cfg.scoreCols': ','.join(config.scoreCols),
                'sqlStr': sqlStr,
                'id'       : id,
                'result'   : 1
            }
            return json.dumps(retData)

        # 登录(表单页已经在static里面了)
        elif name=="public/logindo/":
            i = web.input()
            account = i.get('inputZH')
            passw  = i.get('inputMM')
            if not account or not passw:
                return  { "result":0, "info":"请求参数错误。err 1302" }
            else:
                # 从数据库查询账号密码
                passwMD5 = hashlib.md5(passw.encode('utf8')).hexdigest()
                sql = "SELECT * FROM dept WHERE account='%s' AND passw='%s' " % (account,passwMD5)
                cursor = conn.cursor()
                rs = cursor.execute(sql)
                data = cursor.fetchall()
                if len(data):
                    # 认证通过，session登录状态，进入评价页面
                    # session.account = account
                    session.account += 1
                    # session.dept    = data[0].name
                    web.redirect("/pingjuan_list/")
                else:
                    # 认证失败，返回
                    return "<script>alert('账号不存在或密码错误。错误码0023');window.history.back(-1);</script>"


        else:
            return 'ERROR 13044!'
        

if __name__ == "__main__":
    app.run()